Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Configuration
Imports System.IO
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

Imports Krystalware.SlickUpload
Imports Krystalware.SlickUpload.Configuration
Imports Krystalware.SlickUpload.Storage
Imports Krystalware.SlickUpload.Storage.Streams

Namespace Storage
    ''' <summary>Provides a simple repository that access the file data in SQL.</summary>
    ''' <remarks>When implementing access in your own project, we highly recommended to reimplement
    '''          this functionality using whatever existing data access pattern
    '''          you are using in your project.
    ''' </remarks>
    Public Class SqlFileRepository
        Private Shared _cnString As String
        Private Shared _table As String
        Private Shared _keyField As String
        Private Shared _nameField As String
        Private Shared _dataField As String
        Private Shared _dataType As SqlColumnDataType

        Public Sub New(uploadProfile As String)
            Dim section As UploadStreamProviderElement = SlickUploadContext.Config.UploadProfiles(uploadProfile).UploadStreamProvider

            Dim connectionStringName As String = section.Parameters("connectionStringName")

            If Not String.IsNullOrEmpty(connectionStringName) Then
                _cnString = ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString
            Else
                _cnString = section.Parameters("connectionString")
            End If

            _table = section.Parameters("table")
            _keyField = section.Parameters("keyField")
            _nameField = section.Parameters("fileNameField")
            _dataField = section.Parameters("dataField")

            Dim dataTypeString As String = section.Parameters("dataType")

            If Not String.IsNullOrEmpty(dataTypeString) Then
                _dataType = DirectCast([Enum].Parse(GetType(SqlColumnDataType), dataTypeString, True), SqlColumnDataType)
            Else
                _dataType = SqlColumnDataType.Image
            End If
        End Sub

        Public Function GetDataStream(file As SqlFile) As Stream
            If Not _dataType = SqlColumnDataType.FileStream Then
                Return New SqlClientReadStream(_cnString, _table, _dataField, AddressOf file.BuildCriteria, _dataType)
            Else
                Return New SqlFileStreamReadStream(_cnString, _table, _dataField, AddressOf file.BuildCriteria)
            End If
        End Function

        Public Function GetAll() As List(Of SqlFile)
            Return GetAll(False)
        End Function

        Public Function GetAll(hasCategory As Boolean) As List(Of SqlFile)
            Dim files As List(Of SqlFile) = New List(Of SqlFile)()

            Using cn As IDbConnection = New SqlConnection(_cnString)
                Using cmd As IDbCommand = cn.CreateCommand()
                    cmd.CommandText = "SELECT " + _keyField + ", " + _nameField + ", CAST(DATALENGTH(" + _dataField + ") AS bigint) AS Length"

                    If hasCategory Then
                        cmd.CommandText += ", Category"
                    End If

                    cmd.CommandText += " FROM " + _table

                    cn.Open()

                    Using rd As IDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult)
                        While (rd.Read())
                            If hasCategory Then
                                files.Add(New SqlFile(Convert.ToInt64(rd.GetValue(0)), rd.GetString(1), rd.GetInt64(2), _keyField, rd.GetString(3)))
                            Else
                                files.Add(New SqlFile(Convert.ToInt64(rd.GetValue(0)), rd.GetString(1), rd.GetInt64(2), _keyField))
                            End If
                        End While
                    End Using
                End Using
            End Using

            Return files
        End Function

        Public Function GetById(ByVal id As Long) As SqlFile
            Using cn As IDbConnection = New SqlConnection(_cnString)
                Using cmd As IDbCommand = cn.CreateCommand()
                    cmd.CommandText = "SELECT " + _nameField + ", CAST(DATALENGTH(" + _dataField + ") AS bigint) AS Length FROM " + _table + " WHERE " + _keyField + "=" + id.ToString()

                    cn.Open()

                    Using rd As IDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
                        If rd.Read() Then
                            Return New SqlFile(id, rd.GetString(0), rd.GetInt64(1), _keyField)
                        Else
                            Return Nothing
                        End If
                    End Using
                End Using
            End Using
        End Function
    End Class
End Namespace